#!/bin/bash

# 定义变量方便修改
table_db=cy_database
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql ="
with
tmp1_table1 as
(
select
	cast(alter_time as int) as content,
	sum(turnover) as num
from cy_database.dws_enterprise_business_detail
group by alter_time
),

tmp1_table2 as
(
select
	content,
	num,
	(content-1) as df_time
from tmp1_table1
),


tmp1_table3 as
(
select
	b.num,
	case when a.num is not null then cast((b.num-a.num)/a.num as decimal(10,5))
	else 0 end  as percent1,
	b.content,
	'产业产值总量与年增长量' name,
	cast(current_date() as string) as operate_time
from tmp1_table2 b
left join tmp1_table1 a
on b.df_time = a.content
),



tmp2_table as
(
select
	num,
	null percent1,
	content,
	'岗位需求量' name,
	cast(current_date() as string) as operate_time
from (
	select
		posts_name as content,
		sum(posts) num
	from cy_database.dws_enterprise_position_require_detail
	group by posts_name
	)a
order by num desc
limit 10
),


tmp3_table as 数据没有高科人才基础
(
select
	num,
	null percent1,
	content,
	'异地人才归属地' name,
	cast(current_date() as string) as operate_time
from (
	select
		census_register as content,
		count(1) as num
	from cy_database.dws_person_detail
	where substr(idcard,1,3) = '410'
	group by census_register
	)a
order by num desc
limit 10
),



tmp4_table as
(
select
	num,
	null percent1,
	content,
	'学历分布' name,
	cast(current_date() as string) as operate_time
from (
	select
		content,
		count(name) as num
	from (
		select
			name,
			case when education = '1' then '其他'
				when education = '2' then '其他'
				when education = '3' then '其他'
				when education = '4' then '专科'
				when education = '5' then '本科'
			else '硕士以上' end as content
		from cy_database.dws_person_work_detail
		)a
	group by content
	)aa

),


tmp5_table as 所属产业没有数据支持
(

select
	num,
	null percent1,
	concat_ws('-',content,'起重') content,
	'每年需求人数' as name,
	cast(current_date() as string) as operate_time
from (
	select
		content,
		sum(posts) as num
	from (
		select
			date_format(dt,'yyyy') as content,
			posts
		from cy_database.dws_enterprise_position_require_detail
		where industry_code = '0'
		)a
	group by content
	)aa

union all
select
	num,
	null percent1,
	concat_ws('-',content,'烹饪') content,
	'每年需求人数' as name,
	cast(current_date() as string) as operate_time
from (
	select
		content,
		sum(posts) as num
	from (
		select
			date_format(dt,'yyyy') as content,
			posts
		from cy_database.dws_enterprise_position_require_detail
		where industry_code = '1'
		)a
	group by content
	)aa

union all
select
	num,
	null percent1,
	concat_ws('-',content,'防腐') content,
	'每年需求人数' as name,
	cast(current_date() as string) as operate_time
from (
	select
		content,
		sum(posts) as num
	from (
		select
			date_format(dt,'yyyy') as content,
			posts
		from cy_database.dws_enterprise_position_require_detail
		where industry_code = '2'
		)a
	group by content
	)aa

union all
select
	num,
	null percent1,
	concat_ws('-',content,'卫材') content,
	'每年需求人数' as name,
	cast(current_date() as string) as operate_time
from (
	select
		content,
		sum(posts) as num
	from (
		select
			date_format(dt,'yyyy') as content,
			posts
		from cy_database.dws_enterprise_position_require_detail
		where industry_code = '3'
		)a
	group by content
	)aa
),




tmp6_table as 产业、本地、异地人才、高科技数据基础不支持
(
select
	num,
	null percent1,
	content,
	'各行业就业人才 ' name,
	cast(current_date() as string) as operate_time
from (
	select
		business_code as content,
		count(name) as num
	from cy_database.dws_person_work_detail
	group by business_code
	)a

)




insert overwrite table cy_database.ads_big_screen_result_fazhan
select num,percent1,content,name,operate_time from tmp1_table3

union all
select num,percent1,content,name,operate_time from tmp2_table

union all
select num,percent1,content,name,operate_time from tmp3_table

union all
select num,percent1,content,name,operate_time from tmp4_table

union all
select num,percent1,content,name,operate_time from tmp5_table

union all
select num,percent1,content,name,operate_time from tmp6_table

;



"

$hive -e "$sql"